package h_eekwan.service;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import h_eekwan.interfaces.SungJukService;
import h_eekwan.module.SungJukVO;

public class JDBCSungJuk implements SungJukService{
	// JDBC 접속 정보
	private static final String DRV = "oracle.jdbc.OracleDriver";
	private static final String URL = "jdbc:oracle:thin:@//localhost:1521/xe"; // 오라클은 1521,mysql은 3306 포트
	private static final String USR = "scott";
	private static final String PWD = "TIGER";
	// SQL 문 작성
	private static final String insertSQL = 
			" insert into sungjuk(sjno, name, kor, eng, mat) " +
			" VALUES (SJNO.nextval, ?, ?, ?, ?) ";
	private static final String selectSQL = 
			"select * from sungjuk order by sjno desc";
	
	private static final String updateSQL =
			" update sungjuk set name= ?, kor = ?, eng = ?, mat = ? where sjno = ? ";
	
	private static final String deleteSQL = 
			" delete from sungjuk where sjno = ? ";	// ?는 고정하면안되므로..
	
	public boolean newSungJuk(SungJukVO sj) {
		Connection conn = null;
		PreparedStatement ps = null;
		boolean result = false;
		
		try{
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL, USR, PWD);
			ps = conn.prepareStatement(insertSQL);
				// ? 매개변수에 들어갈 실제입력값을 지정
				ps.setString(1, sj.getName());	// 1번째는 name이니 ?를 getName으로 
				ps.setInt(2, sj.getKor());	// 2번째는 kor이니 ?를 getName으로 
				ps.setInt(3, sj.getEng());	// 3번째는 eng이니 ?를 getName으로 
				ps.setInt(4, sj.getMat());	// 4번째는 mat이니 ?를 getName으로 
			int cnt = ps.executeUpdate();
			
			// 성공적으로 insert 되면 result를 true로
			
			if ( cnt > 0 ) result = true;		// 값받는 순서는 conn->ps->cnt 로받으니까 역순으로 객체를다는다
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			if (ps != null)
				try {ps.close();
				} catch(Exception e){}
			if ( conn != null )
				try {conn.close();
				} catch(Exception e){}
			
			} return result;
		}
	
	
	
	

	@Override
	public String readSungJuk() {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String result = "";
		
		try{
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL, USR, PWD);
			ps = conn.prepareStatement(selectSQL);
			rs = ps.executeQuery(); 	
					// 결과를 받았으면 값찍어보기
			while(rs.next()){
				for(int i = 1; 9 >= i; ++i)			// 1부터실행, 1열~9열까지 조회해야하니
					result += rs.getString(i)+"  ";
					
					result += "\n";
				}					
			
		} catch(Exception ex){
			ex.printStackTrace();			
		} finally{
			if (rs != null)
				try { rs.close();} catch(Exception e){}
			if (ps != null)
				try { ps.close();} catch(Exception e){}
			if (conn != null)
				try { conn.close();} catch(Exception e){}			
			}
		
		return result;
	}

	@Override
	public boolean modifySungJuk(SungJukVO sj) {
		Connection conn = null;
		PreparedStatement ps = null;
		boolean result = false;
		
		try{
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL, USR, PWD);
			ps = conn.prepareStatement(updateSQL);
			ps.setString(1, sj.getName());
			ps.setInt(2, sj.getKor());
			ps.setInt(3, sj.getEng());
			ps.setInt(4, sj.getMat());
			ps.setString(5, sj.getSjno());
			
			int cnt = ps.executeUpdate();
			if (cnt > 0 ) result = true;
			
		}catch ( Exception ex){
			ex.printStackTrace();			
		}finally{
			if( ps != null ) 
				try{ps.close();} catch(Exception e){};
			if( conn != null)
				try{conn.close();} catch(Exception e) {};			
		}
		return result;
	}

	@Override
	public boolean removeSungJuk(String Key) {
		Connection conn = null;
		PreparedStatement ps = null;	// 실행할 sql문을 준비
		boolean result = false;
		
		try{
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL, USR, PWD);
			ps = conn.prepareStatement(deleteSQL);
			ps.setString(1, Key);		// 첫번째 ?에 대입할 값 지정  
			int cnt = ps.executeUpdate();	// ps라는 객체에 들어있는 걸 실행하겠다.
				// sql문 실행 후 결과 반환
			if ( cnt > 0) result = true;			// 하나 이상이라도 삭제되었다면..??
		}catch ( Exception ex){
			ex.printStackTrace();			
		}finally{
			if (ps != null)
				try { ps.close();} catch(Exception e){}
			if (conn != null)
				try { conn.close();} catch(Exception e){}			
			}	// 자원을 반납. ex. 식당에서 식사 후 식판 반납
		
		return false;
	}

	
}
